package com.wuhongyu.entity;

import lombok.*;

import javax.persistence.*;
import java.io.Serializable;
import java.math.BigDecimal;

/**
 * 客户表
 */


//本地查询

/**
 * 这里真的是卡死我了
 */
@SqlResultSetMappings({
        @SqlResultSetMapping(
                name = "Contr",
                columns = {
                        @ColumnResult(name = "id", type = Integer.class),
                        @ColumnResult(name = "name", type = String.class),
                        @ColumnResult(name = "amount", type = BigDecimal.class)
                }
                )

})


@NamedNativeQueries({
        @NamedNativeQuery(
                name = "Customer.findContrAll",
                query = "select customer.id id,customer.name name, sum(orders.amount) amount from customer " +
                        "left join orders on orders.customer_id = customer.id " +
                        "group by customer.id;",
                resultSetMapping = "Contr"
        ),
        @NamedNativeQuery(
                name = "Customer.findContrByName",
                query = "select customer.id id,customer.name name, sum(orders.amount) amount from customer " +
                        "left join orders on orders.customer_id = customer.id where customer.name like ?" +
                        "group by customer.id;",
                resultSetMapping = "Contr"
        ),
        @NamedNativeQuery(
                name = "Customer.findContrByYear",
                query = "select customer.id id,customer.name name, sum(orders.amount) amount from customer " +
                        "left join orders on orders.customer_id = customer.id where DATE_FORMAT(orders.createtime,'%Y') = ? " +
                        "group by customer.id;",
                resultSetMapping = "Contr"
        ),
        @NamedNativeQuery(
                name = "Customer.findContrByYearAndName",
                query = "select customer.id id,customer.name name, sum(orders.amount) amount from customer " +
                        "left join orders on orders.customer_id = customer.id where DATE_FORMAT(orders.createtime,'%Y') = ?" +
                        "and customer.name like ?" +
                        "group by customer.id;",
                resultSetMapping = "Contr"
        )
})


@Entity
@Data
public class Customer implements Serializable {

    private static final long serialVersionUID = 3967736119209703181L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    private String identity;
    private String name;
    private String area;

    private String grade;
    //满意度
    private Integer satisfied;
    //客户信用
    private Integer credit;
    private String address;
    //邮政编码
    private Integer postalcode;
    private String telephone;
    //传真
    private String telautogram;
    //网址
    private String url;
    //营业执照注册号
    private String blrnumber;
    //法人
    private String boss;
    //注册资金
    private BigDecimal fund;
    //营业额/年
    private BigDecimal turnover;
    //开户银行
    private String bank;
    //银行账户
    private String account;
    //地税登记号
    private String landtax;
    //国税登记号
    private String nationaltax;
    private Integer status;

    //客户经理
    @ManyToOne
    @JoinColumn(name = "managerUserId")
    private User managerUser;


}